package com.drool.engine.mapper.oracle;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.drool.engine.entity.HZjbgMx;
import com.drool.engine.entity.vo.Fp0001;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface Fp0001Mapper  extends BaseMapper<Fp0001> {

    //FP0001取数数据项
    @Select("SELECT '发票代码为' || DFZ.FPDM || '发票号码为' || DFZ.FPHM || '的增值税发票上游异常' as  sx1" +

            "    FROM FP20.DZDZ_FPXX_ZZSFP DFZ" +
            "    WHERE DFZ.XFSBH IN" +
            "            (SELECT DN.NSRSBH" +
            "                    FROM HX_DJ.DJ_FZCHRDXX DF, HX_DJ.DJ_NSRXX DN" +
            "    WHERE DN.DJXH = DF.DJXH" +
            "                     AND DF.JCFZCHRQ IS NULL" +
            "            )" +
            "    AND DECODE(DFZ.ZFRQ, NULL, 'N', 'Y') = 'N'" +
            "    AND DFZ.KPRQ BETWEEN to_date(#{qjq},'yyyy-mm-dd hh24:mi:ss') AND  to_date(#{qjz},'yyyy-mm-dd hh24:mi:ss')" +
            "    AND DFZ.GFSBH IN (SELECT to_char(djxh)" +
            "    FROM HX_DJ.DJ_NSRXX" +
            "    WHERE DJXH = #{djxh}" +
            "    UNION ALL" +
            "    SELECT SHXYDM" +
            "    FROM HX_DJ.DJ_NSRXX" +
            "    WHERE DJXH = #{djxh}" +
            "    AND SHXYDM IS NOT NULL)" +
            "    UNION ALL" +
            "    SELECT '发票代码为' || DFP.FPDM || '发票号码为' || DFP.FPHM || '的普通发票上游异常'  as  sx1" +

            "    FROM FP20.DZDZ_FPXX_PTFP DFP" +
            "    WHERE DFP.XFSBH IN (SELECT DN.NSRSBH" +
            "            FROM HX_DJ.DJ_FZCHRDXX DF, HX_DJ.DJ_NSRXX DN" +
            "    WHERE DN.DJXH = DF.DJXH" +
            "                                AND DF.JCFZCHRQ IS NULL" +
            "    )" +
            "    AND DECODE(DFp.ZFRQ, NULL, 'N', 'Y') = 'N'" +
            "    AND DFP.KPRQ BETWEEN  to_date(#{qjq},'yyyy-mm-dd hh24:mi:ss') AND  to_date(#{qjz},'yyyy-mm-dd hh24:mi:ss')" +
            "    AND DFP.GFSBH IN (SELECT NSRSBH" +
            "    FROM HX_DJ.DJ_NSRXX" +
            "            WHERE DJXH = #{djxh}" +
            "    UNION ALL" +
            "    SELECT SHXYDM" +
            "    FROM HX_DJ.DJ_NSRXX" +
            "    WHERE DJXH = #{djxh}" +
            "    AND SHXYDM IS NOT NULL)" +
            "    UNION ALL" +
            "    select '发票代码为' || DFJ.FPDM || '发票号码为' || DFJ.FPHM || '的卷式发票上游异常' as  sx1" +

            "    FROM FP20.DZDZ_FPXX_JSFP DFJ" +
            "    WHERE DFJ.Xfsbh IN (SELECT DN.NSRSBH" +
            "            FROM HX_DJ.DJ_FZCHRDXX DF, HX_DJ.DJ_NSRXX DN" +
            "    WHERE DN.DJXH = DF.DJXH" +
            "                                AND DF.JCFZCHRQ IS NULL" +
            "    )" +
            "    AND DECODE(DFJ.ZFRQ, NULL, 'N', 'Y') = 'N'" +
            "    AND DFJ.KPRQ BETWEEN  to_date(#{qjq},'yyyy-mm-dd hh24:mi:ss') AND  to_date(#{qjz},'yyyy-mm-dd hh24:mi:ss')" +
            "    AND DFJ.Gfsbh IN (SELECT NSRSBH" +
            "    FROM HX_DJ.DJ_NSRXX" +
            "            WHERE DJXH =#{djxh}" +
            "    UNION ALL" +
            "    SELECT SHXYDM" +
            "    FROM HX_DJ.DJ_NSRXX" +
            "    WHERE DJXH = #{djxh}" +
            "    AND SHXYDM IS NOT NULL)" +
            "    UNION ALL" +
            "    SELECT '发票代码为' || DFD.FPDM || '发票号码为' || DFD.FPHM || '的电子发票上游异常' as  sx1" +

            "    FROM FP20.DZDZ_FPXX_DZFP DFD" +
            "    WHERE DFD.Xfsbh IN (SELECT DN.NSRSBH" +
            "            FROM HX_DJ.DJ_FZCHRDXX DF, HX_DJ.DJ_NSRXX DN" +
            "    WHERE DN.DJXH = DF.DJXH" +
            "                                AND DF.JCFZCHRQ IS NULL" +
            "    )" +
            "    AND DFD.ZFRQ IS NULL" +
            "    AND DFD.KPRQ BETWEEN  to_date(#{qjq},'yyyy-mm-dd hh24:mi:ss') AND  to_date(#{qjz},'yyyy-mm-dd hh24:mi:ss')" +
            "    AND DFD.Gfsbh IN (SELECT NSRSBH" +
            "    FROM HX_DJ.DJ_NSRXX" +
            "            WHERE DJXH =#{djxh}" +
            "    UNION ALL" +
            "    SELECT SHXYDM" +
            "    FROM HX_DJ.DJ_NSRXX" +
            "    WHERE DJXH = #{djxh}" +
            "    AND SHXYDM IS NOT NULL)")
    List<HZjbgMx> getFp0001Sjx(@Param("qjq") String qjq , @Param("qjz") String qjz , @Param("djxh") String djxh);
}
